<?php
        header("content-type:text/html;charset=utf-8");
        require_once 'include.php';
        require_once 'PHPExcel/PHPExcel.php';
        require_once 'PHPExcel/PHPExcel/IOFactory.php';
        require_once 'PHPExcel/PHPExcel/Reader/Excel5.php';
/*处理上传的excel文件*/
        if (!empty ($_FILES ['file_stu'] ['name'])) {
            $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
            $file_types = explode(".", $_FILES ['file_stu'] ['name']);
            $file_type = $file_types [count($file_types) - 1];

            /*判别是不是.xls文件，判别是不是excel文件*/
            if (strtolower($file_type) != "xlsx") {
                //$this->error('不是Excel文件，重新上传');
                    echo "不是Excel文件，重新上传";
            }

            /*设置上传路径*/
//$savePath = SITE_PATH . '/public/upfile/Excel/';
            $savePath = './file/';

            /*以时间来命名上传的文件*/
            $str = date('Ymdhis');
            $file_name = $str . "." . $file_type;

            /*是否上传成功*/
            if (!copy($tmp_file, $savePath . $file_name)) {
                //$this->error('上传失败');
                echo "上传失败";
            }
            $filePath=$savePath . $file_name;
            $_SESSION['filePath']=$filePath;
        }

/*把上传的excel文件中的数据存入数据库*/
//实例化PHPExcel类
$PHPExcel = new PHPExcel();
//默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
    $PHPReader = new PHPExcel_Reader_Excel5();
    if (!$PHPReader->canRead($filePath)) {
        echo 'no Excel';
        //return;
    }
}
//读取Excel文件
$PHPExcel = $PHPReader->load($filePath);
//读取excel文件中的第一个工作表
$sheet = $PHPExcel->getSheet(0);
//取得最大的列号
$allColumn = $sheet->getHighestColumn();
//取得最大的行号
$allRow = $sheet->getHighestRow();
$i=0;
while(chr(65+$i)<=$allColumn)
{
    $list_item[] = $PHPExcel->getActiveSheet()->getCell(chr(65+$i) . 1)->getValue();
    $i++;
}
$list_num=count($list_item);
/*向type表里插入表名*/
$form_name=$_POST['form_name'];
$_SESSION['form_name']=$_POST['form_name'];
$array = array(
    "type" => $form_name
);
$rs= new DBHelper();
$rs->insert('type',$array);
/*新建一个数据表*/
switch ($list_num) {
    case 1:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 2:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 3:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 4:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 5:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 6:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 7:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 8:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 9:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL,
      $list_item[8] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    case 10:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL,
      $list_item[8] VARCHAR(100) NOT NULL,
      $list_item[9] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
    break;
    case 11:
      $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL,
      $list_item[8] VARCHAR(100) NOT NULL,
      $list_item[9] VARCHAR(100) NOT NULL,
      $list_item[10] VARCHAR(100) NOT NULL)";
      $rs->query_t($sql);
    break;
    case 12:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL,
      $list_item[8] VARCHAR(100) NOT NULL,
      $list_item[9] VARCHAR(100) NOT NULL,
      $list_item[10] VARCHAR(100) NOT NULL,
      $list_item[11] VARCHAR(100) NOT NULL)";

        $rs->query_t($sql);
        break;
    case 13:
        $sql = "CREATE TABLE $form_name (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      $list_item[0] VARCHAR(249) NOT NULL,
      $list_item[1] VARCHAR(100) NOT NULL,
      $list_item[2] VARCHAR(100) NOT NULL,
      $list_item[3] VARCHAR(100) NOT NULL,
      $list_item[4] VARCHAR(100) NOT NULL,
      $list_item[5] VARCHAR(100) NOT NULL,
      $list_item[6] VARCHAR(100) NOT NULL,
      $list_item[7] VARCHAR(100) NOT NULL,
      $list_item[8] VARCHAR(100) NOT NULL,
      $list_item[9] VARCHAR(100) NOT NULL,
      $list_item[10] VARCHAR(100) NOT NULL,
      $list_item[11] VARCHAR(100) NOT NULL,
      $list_item[12] VARCHAR(100) NOT NULL)";
        $rs->query_t($sql);
        break;
    default:
        var_dump("default");
}
/*往刚建的数据表添加内容*/
//从第二行开始插入,第一行是列名
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
    $all_Column = ord($allColumn)-64;
    for($i=0;$i<$all_Column;$i++){
        //获取$i列的值
        $line[$i] = $PHPExcel->getActiveSheet()->getCell(chr(65+$i) . $currentRow)->getValue();
    }
    for ($k=0;$k<$all_Column;$k++){
        $arr[$list_item[$k]] = $line[$k];
    }
        $rs = new DBHelper();
        $rs->insert($_POST['form_name'],$arr);
}
/*删除源文件*/
$result = unlink($_SESSION['filePath']);
if($result){
    echo "删除数据表成功";
}